<?php
class Issue extends Zend_Db_Table
{
	protected $_name = "issue";
	
	public function getStatus($id)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name),null)
							->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus',
														array('issueStatus'=>'pname'))
							->where('issue.id = ?',$id);
		$result = $this->getAdapter()->fetchOne($select);
		return $result;
	}
	public function getIssuesCount($where)
	{
		$select = $this->_db->select()
							->from($this->_name,array('count(*) as c'))
							->where($where);
		$result = $this->getAdapter()->fetchOne($select);
		return $result;
	}
	
	public function numRows($pid)
	{
		$select = $this->_db->select()
							->from($this->_name,array('count(*)'))
							->where('project = ?', $pid);
		$result = $this->getAdapter()->fetchOne($select);
		return $result;
	}
	public function getIssues($where, $order=null, $type="DESC")
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name))
							->joinLeft(array('priority'=>'priority'),'
												issue.priority=priority.id',
														array('priorityName'=>'pname',
															  'priorityIcon'=>'icon_url',
															  'priorityDesc' => 'description'))
							->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus',
													array('issueStatusName'=>'pname',
														  'issueStatusIcon'=>'icon_url',
														  'issueStatusDesc' => 'description'))
							->joinLeft(array('resolution'=>'resolution'),
											'issue.resolution=resolution.id',
														array('resolutionName'=>'pname',
														'resolutionDesc' => 'description'))
							->joinLeft(array('issuetype'=>'issuetype'),
											'issue.issuetype=issuetype.id',
													array('issueTypeName'=>'pname',
														  'issueTypeIcon'=>'icon_url',
														   'issueTypeDesc' => 'description'));
		 $select->where($where);
		 if ($order) {
		 	$select->order("$order $type");
		 }
		//echo $select;exit;
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	public function getIssueData($id)
	{
		$select = $this->_db->select()
						->from(array('issue'=>$this->_name))
						->joinLeft(array('priority'=>'priority'),'issue.priority=priority.id')
						->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus')
						->joinLeft(array('resolution'=>'resolution'),
											'issue.resolution=resolution.id')
						->group('issue.id')
						->where('id = ?', $id);
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getIssuesByUser($user)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name),
										array('id','pkey','issueType','summary','priority'))
							->joinLeft(array('issuetype'=>'issuetype'),
											'issue.issuetype=issuetype.id',
													array('issueTypeName'=>'pname',
														  'issueTypeIcon'=>'icon_url',
														   'issueTypeDesc' => 'description'))
							->joinLeft(array('priority'=>'priority'),'
												issue.priority=priority.id',
														array('priorityName'=>'pname',
															  'priorityIcon'=>'icon_url',
															  'priorityDesc' => 'description'))
							->where('issue.assignee = ?', $user);
							
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getIssuesInProgress($user)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name),
										array('id', 'pkey','issueType','summary','priority'))
							->joinLeft(array('issuetype'=>'issuetype'),
											'issue.issuetype=issuetype.id',
													array('issueTypeName'=>'pname',
														  'issueTypeIcon'=>'icon_url',
														   'issueTypeDesc' => 'description'))
							->joinLeft(array('priority'=>'priority'),'
												issue.priority=priority.id',
														array('priorityName'=>'pname',
															  'priorityIcon'=>'icon_url',
															  'priorityDesc' => 'description'))
							->joinLeft(array('issuestatus'=>'issuestatus'),
												'issue.issuestatus = issuestatus.id',
														array('pname'))
							->where('issue.assignee = ?', $user)
							->where('issuestatus.pname = "In Progress"');
							
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getIssueRow($id)
	{
		$select = $this->_db->select()
						->from(array('issue'=>$this->_name))
						->joinLeft(array('priority'=>'priority'),'
												issue.priority=priority.id',
														array('priorityName'=>'pname',
															  'priorityIcon'=>'icon_url',
															  'priorityDesc' => 'description'))
						->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus',
													array('issueStatusName'=>'pname',
														  'issueStatusIcon'=>'icon_url',
														  'issueStatusDesc' => 'description'))
						->joinLeft(array('resolution'=>'resolution'),
											'issue.resolution=resolution.id',
														array('resolutionName'=>'pname',
														'resolutionDesc' => 'description'))
						->joinLeft(array('issuetype'=>'issuetype'),
											'issue.issuetype=issuetype.id',
													array('issueTypeName'=>'pname',
														  'issueTypeIcon'=>'icon_url',
														   'issueTypeDesc' => 'description'))
						->group('issue.id')
						->where('issue.id = ?', $id);
		$results = $this->getAdapter()->fetchRow($select);
		return $results;
	}
	
	public function getIssuesByComponentId($cid)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name))
							/*->join(array('comp_issues'=>'component_issues'),
												'issue.id = comp_issues.issue',null)*/
							->join(array('nodeAssoc'=>'nodeassociation'),
												'nodeAssoc.source_node_id = issue.id',null)
							->joinLeft(array('priority'=>'priority'),'
												issue.priority=priority.id',
														array('priorityName'=>'pname',
															  'priorityIcon'=>'icon_url',
															  'priorityDesc' => 'description'))
							->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus',
													array('issueStatusName'=>'pname',
														  'issueStatusIcon'=>'icon_url',
														  'issueStatusDesc' => 'description'))
							->joinLeft(array('resolution'=>'resolution'),
											'issue.resolution=resolution.id',
														array('resolutionName'=>'pname',
														'resolutionDesc' => 'description'))
							->joinLeft(array('issuetype'=>'issuetype'),
											'issue.issuetype=issuetype.id',
													array('issueTypeName'=>'pname',
														  'issueTypeIcon'=>'icon_url',
														   'issueTypeDesc' => 'description'))
							->join(array('component'=>'component'),
												'component.id = nodeAssoc.sink_node_id',null)
							->where('nodeAssoc.association_type = "IssueComponent"')
							->where('component.id=?',$cid);
		
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getOpenIssuesData($pid=null)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name),array('count(*) as c'))
							->joinLeft(array('priority'),
												'issue.priority = priority.id',
														array('priority' => 'pname',
															  'priorityIcon' => 'icon_url'))
							->group('issue.priority')
							->where('project = ?', $pid);
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getOpenIssuesByAssignee($pid=null)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name),array('count(*) as c'))
							->joinLeft(array('propertyString'=>'propertystring'),
												'issue.assignee = propertyString.propertyvalue',
																array('propertyvalue as assignee'))
							/*->join(array('userBase'=>'userBase'),
												'userBase.name = issue.assignee'
												,array('name'))
							->join(array('propertyEntry'=>'propertyentry'),
												'userBase.id = propertyEntry.entry_id',
																	array('property_key'))*/
							->group('issue.assignee')
							->where('project = ?', $pid);
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getComponentOpenIssuesData($pid=null, $cid = null)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name),array('count(*) as c'))
							->join(array('nodeAssoc'=>'nodeassociation'),
												'nodeAssoc.source_node_id = issue.id')
							->joinLeft(array('priority'),
												'issue.priority = priority.id',
														array('priority' => 'pname',
															  'priorityIcon' => 'icon_url'))
							->group('issue.priority')
							->where('project = ?', $pid)
							->where('nodeAssoc.sink_node_id = ?',$cid)
							->where('nodeAssoc.association_type = "IssueComponent"');
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getVersionOpenIssues($pid=null, $vid = null)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name),array('count(*) as c'))
							->join(array('nodeAssoc'=>'nodeassociation'),
												'nodeAssoc.source_node_id = issue.id')
							->joinLeft(array('priority'),
												'issue.priority = priority.id',
														array('priority' => 'pname',
															  'priorityIcon' => 'icon_url'))
							->group('issue.priority')
							->where('project = ?', $pid)
							->where('nodeAssoc.sink_node_id = ?',$vid)
							->where('nodeAssoc.association_type = "IssueFixVersion"' );
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getIssueSummaryData($pid=null)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name),array('count(*) as c'))
							->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus',
													array('issueStatusName'=>'pname',
														  'issueStatusIcon'=>'icon_url',
														  'issueStatusDesc' => 'description'))
							->group('issue.issuestatus')
							->where('project = ?', $pid);
		//echo $select;
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getComponentSummaryData($pid = null,$cid = null)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name),array('count(*) as c'))
							->join(array('nodeAssoc'=>'nodeassociation'),
												'nodeAssoc.source_node_id = issue.id')
							//->join(array('comp'=>'component','comp.id = nodeAssoc.sink_node_id'),null)					
							->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus',
													array('issueStatusName'=>'pname',
														  'issueStatusIcon'=>'icon_url',
														  'issueStatusDesc' => 'description'))
							->group('issue.issuestatus')
							->where('issue.project = ?', $pid)
							->where('nodeAssoc.sink_node_id = ?',$cid)
							->where('nodeAssoc.association_type = "IssueComponent"');
		//echo $select;
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getVersionSummary($pid = null,$vid = null)
	{
		$select = $this->_db->select()
							->distinct()
							->from(array('issue'=>$this->_name),array('count(*) as c'))
							->join(array('nodeAssoc'=>'nodeassociation'),
												'nodeAssoc.source_node_id = issue.id',null)
							//->join(array('comp'=>'component','comp.id = nodeAssoc.sink_node_id'),null)					
							->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus',
													array('issueStatusName'=>'pname',
														  'issueStatusIcon'=>'icon_url',
														  'issueStatusDesc' => 'description'))
							->group('issue.issuestatus')
							->where('issue.project = ?', $pid)
							->where('nodeAssoc.sink_node_id = ?',$vid)
							->where('nodeAssoc.association_type = "IssueFixVersion"');
		//echo $select;
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getVersionIssues($id) 
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name))
							->joinLeft(array('priority'=>'priority'),'
												issue.priority=priority.id',
														array('priorityName'=>'pname',
															  'priorityIcon'=>'icon_url',
															  'priorityDesc' => 'description'))
							->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus',
													array('issueStatusName'=>'pname',
														  'issueStatusIcon'=>'icon_url',
														  'issueStatusDesc' => 'description'))
							->joinLeft(array('resolution'=>'resolution'),
											'issue.resolution=resolution.id',
														array('resolutionName'=>'pname',
														'resolutionDesc' => 'description'))
							->joinLeft(array('issuetype'=>'issuetype'),
											'issue.issuetype=issuetype.id',
													array('issueTypeName'=>'pname',
														  'issueTypeIcon'=>'icon_url',
														   'issueTypeDesc' => 'description'))
							->join(array('nodeAssoc'=>'nodeassociation'),'issue.id = nodeAssoc.source_node_id',null)
							->group('issue.id')
							->where("association_type = 'IssueFixVersion'")
							//->where('sink_node_entity = "Version" OR sink_node_entity = "Fix Version"')
							->where('nodeAssoc.sink_node_id = ?', $id);
							
		$results = $this->getAdapter()->fetchAll($select);
		return $results;	
	}
	
	public function getSingleVersionIssues($componentId, $vid)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name))
							->joinLeft(array('priority'=>'priority'),'
												issue.priority=priority.id',
														array('priorityName'=>'pname',
															  'priorityIcon'=>'icon_url',
															  'priorityDesc' => 'description'))
							->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus',
													array('issueStatusName'=>'pname',
														  'issueStatusIcon'=>'icon_url',
														  'issueStatusDesc' => 'description'))
							->joinLeft(array('resolution'=>'resolution'),
											'issue.resolution=resolution.id',
														array('resolutionName'=>'pname',
														'resolutionDesc' => 'description'))
							->joinLeft(array('issuetype'=>'issuetype'),
											'issue.issuetype=issuetype.id',
													array('issueTypeName'=>'pname',
														  'issueTypeIcon'=>'icon_url',
														   'issueTypeDesc' => 'description'))
							->join(array('nodeAssoc'=>'nodeassociation'),'issue.id = nodeAssoc.source_node_id',null)
							->group('issue.id')
							->where("nodeAssoc.sink_node_id = ?", $componentId)
							->where("nodeAssoc.association_type = 'IssueComponent' AND (nodeAssoc.source_node_id IN (select nodeassociation.source_node_id from nodeassociation where nodeassociation.sink_node_id = $vid AND nodeassociation.association_type = 'IssueFixVersion'))");
						//->where('nodeAssoc.source_node_id = ?',"(SELECT  nodeassociation.source_node_id FROM nodeassociation WHERE nodeassociation.sink_node_id = $cid AND nodeassociation.association_type = 'IssueComponent')");
						//->orwhere('nodeAssoc.sink_node_id = ?', $cid);
		//echo $select; exit;
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getSingleComponentIssues($versionId, $cid)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name))
							->joinLeft(array('priority'=>'priority'),'
												issue.priority=priority.id',
														array('priorityName'=>'pname',
															  'priorityIcon'=>'icon_url',
															  'priorityDesc' => 'description'))
							->joinLeft(array('issuestatus'=>'issuestatus'),
											'issuestatus.id = issue.issuestatus',
													array('issueStatusName'=>'pname',
														  'issueStatusIcon'=>'icon_url',
														  'issueStatusDesc' => 'description'))
							->joinLeft(array('resolution'=>'resolution'),
											'issue.resolution=resolution.id',
														array('resolutionName'=>'pname',
														'resolutionDesc' => 'description'))
							->joinLeft(array('issuetype'=>'issuetype'),
											'issue.issuetype=issuetype.id',
													array('issueTypeName'=>'pname',
														  'issueTypeIcon'=>'icon_url',
														   'issueTypeDesc' => 'description'))
							->join(array('nodeAssoc'=>'nodeassociation'),'issue.id = nodeAssoc.source_node_id',null)
							->group('issue.id')
							->where("nodeAssoc.sink_node_id = ?", $versionId)
							->where("nodeAssoc.association_type = 'IssueFixVersion' AND (nodeAssoc.source_node_id IN (select nodeassociation.source_node_id from nodeassociation where nodeassociation.sink_node_id = $cid AND nodeassociation.association_type = 'IssueComponent'))");
						//->where('nodeAssoc.source_node_id = ?',"(SELECT  nodeassociation.source_node_id FROM nodeassociation WHERE nodeassociation.sink_node_id = $cid AND nodeassociation.association_type = 'IssueComponent')");
						//->orwhere('nodeAssoc.sink_node_id = ?', $cid);
		//echo $select; exit;
		$results = $this->getAdapter()->fetchAll($select);
		return $results;
	}
	
	public function getUnscheduledIssues($pid)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name), "count(*) as v_issues")
							->where('issue.id NOT IN (SELECT source_node_id from nodeassociation
													where association_type = "IssueFixVersion")')
							->where('issue.project = ?', $pid);
		//echo $select;
		$result = $this->getAdapter()->fetchOne($select);
		return $result;
	}
	public function getNoComponentIssues($pid)
	{
		$select = $this->_db->select()
							->from(array('issue'=>$this->_name), "count(*) as v_issues")
							->where('issue.id NOT IN (SELECT source_node_id from nodeassociation
													where association_type = "IssueComponent")')
							->where('issue.project = ?', $pid);
		//echo $select;
		$result = $this->getAdapter()->fetchOne($select);
		return $result;
	}
	
}